package org.flower.dao.impl;

import org.flower.dao.CommodityDetailsDao;
import org.flower.entity.CommodityClass;
import org.flower.entity.CommodityDetails;
import org.flower.utils.BaseDao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author Aizen
 */
public class CommodityDetailsDaoImpl extends BaseDao implements CommodityDetailsDao {
    @Override
    public List<CommodityDetails> getByComm(int id) {
        String sql ="select * from commodityDetails as c inner join `order` as o on c.id=o.id where o.`userId`=?";
        List<CommodityDetails> list = new ArrayList<CommodityDetails>();
        ResultSet rs = executeQuery(sql,id);
        try {
            while (rs.next()){
               CommodityDetails c = new CommodityDetails(
                       rs.getInt("id"),
                       rs.getString("ComName"),
                       rs.getString("Content"),
                       rs.getInt("outNum"),
                       rs.getDouble("outPrice"),
                       rs.getDouble("marketPrice"),
                       rs.getString("MaterialScience"),
                       rs.getString("packing"),
                       rs.getString("Distribution"),
                       rs.getString("comImage"),
                       rs.getString("detailsImage"),
                       rs.getInt("comShowId"),
                       rs.getInt("ClassId"),
                       rs.getInt("stockd"),
                       rs.getDate("insertDate"));
                String ress[]=c.getComImage().split(",");
                c.setComImage(ress[0]);
                list.add(c);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<CommodityDetails> getCommodityDetails() {
        List<CommodityDetails> list = new ArrayList<CommodityDetails>();
        String sql = "SELECT * FROM commoditydetails ";
        ResultSet rs = executeQuery(sql);
        try {
            while (rs.next()){
                list.add(new CommodityDetails(rs.getInt("id"),
                        rs.getString("ComName"),
                        rs.getString("Content"),
                        rs.getInt("outNum"),
                        rs.getDouble("outPrice"),
                        rs.getDouble("marketPrice"),
                        rs.getString("MaterialScience"),
                        rs.getString("packing"),
                        rs.getString("Distribution"),
                        rs.getString("comImage"),
                        rs.getString("detailsImage"),
                        rs.getInt("comShowId"),
                        rs.getInt("ClassId"),
                        rs.getInt("stockd"),
                        rs.getDate("insertDate")));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public int getTotalCount() {
        int result = -1;
        String sql = "SELECT count(1) c FROM `commoditydetails` ";
        ResultSet rs = executeQuery(sql);
        try {
            if (rs.next()){
                result = rs.getInt("c");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return result;
    }


    @Override
    public int getDayAddGoodsCount() {
        int result = -1;
        String sql = "SELECT COUNT(1) c FROM commoditydetails WHERE TO_DAYS(insertDate) = TO_DAYS(NOW())";
        ResultSet rs = executeQuery(sql);
        try {
            if(rs.next()){
                result = rs.getInt("c");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return  result;
    }

    @Override
    public List<Integer> getZhouGoods() {
        List<Integer> list = new ArrayList<>();
        String sql = "SELECT COUNT(1) c FROM `commoditydetails` WHERE TO_DAYS(insertDate) = TO_DAYS(SUBDATE(NOW(), ?))";
        for (int i = 0; i <= 7; i++) {
            ResultSet rs = executeQuery(sql, i);
            try {
                if(rs.next()){
                    list.add(rs.getInt("c"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                try {
                    closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        return list;
    }

    /**
     * 通过id获取comdetails
     *
     * @param id id
     * @return {@link CommodityDetails}
     */
    @Override
    public CommodityDetails getComdetailsById(int id) {
        String sql = "select d.*,c.name from commoditydetails d inner join commodityclass c on c.id=d.classId where d.id=?";
        CommodityDetails com = null;
        ResultSet rs = executeQuery(sql,id);
        try {
            if(rs.next()){
                com = new CommodityDetails(rs.getInt("id"),rs.getString("ComName"),
                        rs.getString("Content"),rs.getInt("outNum"),
                        rs.getDouble("outPrice"),rs.getDouble("marketPrice"),
                        rs.getString("MaterialScience"),rs.getString("packing"),
                        rs.getString("Distribution"),rs.getString("comImage"),
                        rs.getString("detailsImage"),rs.getInt("comShowId"),
                        rs.getInt("ClassId"),rs.getInt("stockd"),
                        rs.getDate("insertDate"));
                //创建商品类别对象
                CommodityClass cmdcs = new CommodityClass();
                cmdcs.setId(rs.getInt("ClassId"));
                cmdcs.setName(rs.getString("name"));
                //将商品类别信息添加到商品详情中
                com.setComClass(cmdcs);
                //分割商品描述信息
                String content = rs.getString("Content");
                String[] splits = content.split("split");
                com.setContent(splits[0]);
                com.setContent2(splits[1]);
                com.setClassName(rs.getString("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return com;
    }

    @Override
    public boolean deleteCommodityById(int cid) {
        String sql ="DELETE FROM commoditydetails WHERE id = ?";
        return executeUpdate(sql,cid);
    }

    @Override
    public boolean updateCommodity(CommodityDetails c) {
        String sql = "UPDATE commoditydetails SET `ComName`=?,`content`=?,`outNum`=?,`outPrice`=?,`marketPrice`=?,`MaterialScience`=?,`packing`=?,`Distribution`=?,`comImage`=?,`detailsImage`=?,`comShowId`=?,`ClassId`=?,`stockd`=? WHERE `id` = ?";
        return executeUpdate(sql,c.getComName(),c.getContent(),c.getOutNum(),c.getOutPrice(),c.getMarketPrice(),c.getMaterialScience(),c.getPacking(),c.getDistribution(),c.getComImage(),c.getDetailsImage(),c.getComShowId(),c.getClassId(),c.getStockd(),c.getId());
    }

    @Override
    public boolean addCommodity(org.flower.bean.CommodityDetails c) {
        String sql = "INSERT INTO commoditydetails VALUES (NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Date date = new Date();
        return executeUpdate(sql,c.getComname(),c.getContent(),c.getOutnum(),c.getOutprice(),c.getMarketprice(),c.getMaterialscience(),c.getPacking(),c.getDistribution(),c.getComimage(),c.getDetailsimage(),c.getComshowid(),c.getClassid(),c.getStockd(),date);
    }

    @Override
    public int getCommodityStockdById(int id) {
        int stockd = 0;
        String sql = "select stockd from commoditydetails where id =?";
        ResultSet rs = super.executeQuery(sql,id);
        try {
            if (rs.next()){
                stockd = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return stockd;
    }

    @Override
    public String getCommodityNameById(int id) {
        String sql = "select comName from commoditydetails where id =?";
        String strName = "";
        ResultSet rs = super.executeQuery(sql,id);
        try {
            if (rs.next()){
                strName = rs.getString(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return strName;
    }

    @Override
    public List<CommodityDetails> getCommodityDetailsByShowId(int id, int showId) {
        String sql = "select * from commoditydetails where id!=? and comShowId=?";
        List<CommodityDetails> comList = new ArrayList<CommodityDetails>();
        ResultSet rs = executeQuery(sql,id,showId);
        try {
            while(rs.next()){
                CommodityDetails com = new CommodityDetails(rs.getInt("id"),rs.getString("ComName"),
                        rs.getString("Content"),rs.getInt("outNum"),
                        rs.getDouble("outPrice"),rs.getDouble("marketPrice"),
                        rs.getString("MaterialScience"),rs.getString("packing"),
                        rs.getString("Distribution"),rs.getString("comImage"),
                        rs.getString("detailsImage"),rs.getInt("comShowId"),
                        rs.getInt("ClassId"),rs.getInt("stockd"),
                        rs.getDate("insertDate"));
                //重新获取商品图片信息
                String img = com.getComImage().split(",")[0];
                com.setComImage(img);
                //将商品添加到商品集合中
                comList.add(com);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return comList;
    }

    @Override
    public Integer getMaxNo() {
        int maxNo = 0;
        String sql = "SELECT MAX(id) FROM commoditydetails";
        ResultSet rs = executeQuery(sql);
        try {
            if(rs.next()){
                maxNo = rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                closeAll(rs,rs.getStatement(),rs.getStatement().getConnection());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return maxNo;
    }

    @Override
    public boolean upataStockdById(int comId,int stockdNum) {
        String sql = "UPDATE commoditydetails SET stockd=stockd+ ? WHERE id = ?";
        return executeUpdate(sql,stockdNum,comId);
    }

    @Override
    public boolean updateOutNumber(int comid, int outNum) {
        String sql = "UPDATE commoditydetails SET outNum=outNum+ ? WHERE id = ?";
        return executeUpdate(sql,outNum,comid);
    }


}
